-- @owner: @qumin20145
-- @date: 2022-06-15
-- @testpoint: 目标列使用常量/序列/case when表达式/子查询时结合group by使用

--step1:建表并插入数据;expect:成功
drop table if exists t_ustore_query_0065 cascade ;
create table t_ustore_query_0065(id int constraint pk_ustore_query_0065  primary key,c_int int,c_vchar varchar(55) not null,c_vchar2 varchar(55) not null,c_blob blob not null,c_date date)
with (storage_type=ustore) partition by range(id)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300)
);

insert into t_ustore_query_0065 values(1,100,'abc123',lpad('123abc',50,'abc'),lpad('11100651',50,'1100')::blob,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
create or replace procedure proc_ustore_query_0065(tname varchar,startall int,endall int) as
sqlst varchar(500);
begin
  for i in startall..endall loop
  sqlst := 'insert into ' || tname ||' select id+'||i||',c_int+'||i||',c_vchar||'||i||',c_vchar2||'||i||',c_blob'||',c_date from '||tname|| ' where id=1';
        execute immediate sqlst;
  end loop;
end;
/
call proc_ustore_query_0065('t_ustore_query_0065',1,50);

--step2:创建序列;expect:成功
drop sequence if exists seq_ustore_query_0065;
create sequence seq_ustore_query_0065 start with 1 maxvalue 1000 increment by 1 cycle;

--step3:目标列使用常量时结合group by使用;expect:成功
select 1 from t_ustore_query_0065 where id<10 group by id order by id;
select 1,'baiuhoi' from t_ustore_query_0065 where id<10 group by id order by id;

--step4:目标列使用序列时结合group by使用;expect:成功
select seq_ustore_query_0065.nextval num from t_ustore_query_0065 where id<10 group by id order by id;

--step5:目标列使用普通函数表达式时结合group by使用;expect:成功
select id,abs(id) from t_ustore_query_0065 where id<10 group by id order by id;
select id,to_char(id) from t_ustore_query_0065 where id<10 group by id order by id;

--step6:目标列使用case when表达式时结合group by使用;expect:成功
select (case when id=1 then id when id =3 then c_int when id=5 then 1 else 0 end)
from t_ustore_query_0065 where id<10 group by id,c_int,c_vchar order by id;

--step7:目标列使用子查询时结合group by使用;expect:成功
select id,(select sum(c_int) from t_ustore_query_0065 where id<10 group by id,c_int limit 1) from t_ustore_query_0065 where id<10 group by id,c_int order by id;

--step8:清理环境; expect:成功
drop table t_ustore_query_0065 cascade;
drop procedure proc_ustore_query_0065;
drop sequence seq_ustore_query_0065;